/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package IDSNN_1_Data;

import java.sql.Connection;
import java.sql.DriverManager;
import com.mysql.jdbc.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Set;


/**
 *
 * @author francisco
 */
public class DBConnect {
// <editor-fold defaultstate="collapsed" desc="Variables">
    private Connection conn;
    private String url;
    private String dbName;
    private String driver;
    private String userName;
    private String password;
    private String path;
    private Pair pair;
    private ArrayList<Integer> nPorts;
    private ArrayList<Integer> randomList;
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="Constructors">
    public DBConnect(String password, String username, String url, String path){
          conn = null;
          if(url.contains("jdbc"))
            this.url=url;
        else
            this.url = "jdbc:mysql://"+url+"/";
          dbName = "scandetection";
          driver = "com.mysql.jdbc.Driver";
          this.userName = username;
          this.password = password;
          randomList = new ArrayList<Integer>();
          nPorts = new ArrayList<Integer>();
          pair = new Pair();
          this.path = path;
    }
// </editor-fold>

  // <editor-fold defaultstate="collapsed" desc="Instance Methods">
  public int checkExistingMain(String ip1, String ip2)
  {
      int id=-1;
      try
      {
          String query = "Select cod from source where ips='"+ip1+"' and ipd ='"+ip2+"'";
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            id = rs.getInt("cod");
          }
          st.close();
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
          return id;
      }
  }

  public int checkExistingPort(int cod, String p)
  {
      int id=-1;
      try
      {
          String query = "Select codP from ports where cod like '"+cod+"' and PD like '"+p+"'";
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            id = rs.getInt("codP");
          }
          st.close();
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
        return id;
      }
  }

  public int checkExistingFlags(int cod)
  {
      int id=-1;
      try
      {
          String query = "Select syn from flags where cod like "+cod+"";
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            id = rs.getInt("syn");
          }
          st.close();
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
        return id;
      }
  }

  public void addMainTable(String ips, String ipd)
  {
      try
      {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          st.executeUpdate("INSERT into source (ips,ipd) VALUES('"+ips+"','"+ipd+"')");
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
         
      }
  }

  public void addMainPort(int cod, String pd)
  {
      try
      {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          st.executeUpdate("INSERT into ports (cod,PD) VALUES("+cod+",'"+pd+"')");
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
      }
  }
  
  public void addWindow(int cod, String window)
  {
      try
      {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          st.executeUpdate("INSERT into window (cod,window) VALUES("+cod+",'"+window+"')");
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
      }
  }

  public void addFlag(int cod,int syn, int rst, int syn_ack, int fin, int icmp) {
      try
      {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          st.executeUpdate("INSERT into flags (cod,syn,rst,synack,fin,icmp) "+
                  "VALUES("+cod+","+syn+","+rst+","+syn_ack+","+fin+","+icmp+")");
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
      }
  }

  public void updateFlag(int cod,int syn, int rst, int syn_ack, int fin, int icmp) {
      try
      {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          st.executeUpdate("Update flags "+
                  "set syn=syn+"+syn+", rst=rst+"+rst+
                  ", synack=synack+"+syn_ack+", fin=fin+"+fin+", icmp=icmp+"+icmp+" where cod = "+cod);
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
      }
  }
  // </editor-fold>

  // <editor-fold defaultstate="collapsed" desc="leitura para rede neuronal">
  public int randomCod()
  {
      int id=-1;
      String query="SELECT scandetection.source.cod as cod FROM scandetection.source ";
              if(randomList.size()!=0)
                  query += "where cod>0 ";
      for(int value : randomList){
          query +=" AND cod not like "+ value;
      }
      query += " ORDER BY RAND() LIMIT 1";
      
      try
      {   
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            id = rs.getInt("cod");
          }
          st.close();
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
          return id;
      }
  }

  public void clearArrayList()
  {
      randomList.clear();
  }
  
  public Pair getPair(int cod)
  {
      if(cod==-1)
      {
          randomList.clear();
          randomCod();
      }
      int id=-1;
      double[] data = new double[8];
      double[] data2 = new double[8];
      double[] last = new double[13];
      String ips ="";
      String ipd ="";
      randomList.add(cod);
      try
      {
          String query = "select source.cod as cod, source.ips as ips, source.ipd as ipd, count(ports.pd) as pd, "+
       " flags.syn as syn, flags.rst as rst, "+
       " flags.synack as synack, flags.fin as fin, "+
       " flags.icmp as icmp, count(distinct(window)) as window from scandetection.source inner join scandetection.ports on "+
       " ports.cod like source.cod "+
       " inner join scandetection.flags on flags.cod like source.cod "+
       " inner join scandetection.window on window.cod like source.cod "+
       " where source.cod like "+cod;
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            ips = rs.getString("ips");
            ipd = rs.getString("ipd");
            id = rs.getInt("cod");
            data[0] =rs.getDouble("pd");
            data[1] =rs.getDouble("syn");
            data[2] =rs.getDouble("synack");
            data[3] =rs.getDouble("rst");
            data[4] =rs.getDouble("fin");
            data[5] =rs.getDouble("window");
            data[6] =0;
            data[7] =rs.getDouble("icmp");
          }
          st.close();
          conn.close();
          int cod2 = checkExistingMain(ipd, ips);
          query = "select source.ips as ips, source.ipd as ipd, count(ports.pd) as pd, "+
       " flags.syn as syn, flags.rst as rst, "+
       " flags.synack as synack, flags.fin as fin, "+
       " flags.icmp as icmp, count(distinct(window)) as window from scandetection.source inner join scandetection.ports on "+
       " ports.cod like source.cod "+
       " inner join scandetection.flags on flags.cod like source.cod "+
       " inner join scandetection.window on window.cod like source.cod "+
       " where source.cod like "+cod2;
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          st = (Statement) conn.createStatement();
          rs = st.executeQuery(query);
          randomList.add(cod2);
          while (rs.next())
          {
            data2[0] =rs.getDouble("pd");
            data2[1] =rs.getDouble("syn");
            data2[2] =rs.getDouble("synack");
            data2[3] =rs.getDouble("rst");
            data2[4] =rs.getDouble("fin");
            data2[5] =rs.getDouble("window");
            data2[6] =0;
            data2[7] =rs.getDouble("icmp");
          }
          st.close();
          conn.close();      
          if(data[1]>=data2[1] && data[2]>=data2[2])
          {
              last[0]=data[0];
              last[1]=data2[0];
              last[2]=data[1];
              last[3]=data[2];
              last[4]=data2[1];
              last[5]=data2[2];
              last[6]=data[3];
              last[7]=data[4];
              last[8]=data2[4];
              last[9]=data2[5]+data2[5];
              last[10]=data[7];
          }
          else
          {
              String aux = ipd;
              ipd=ips;
              ips=aux;
              last[0]=data2[0];
              last[1]=data[0];
              last[2]=data2[1];
              last[3]=data2[2];
              last[4]=data[1];
              last[5]=data[2];
              last[6]=data2[3];
              last[7]=data2[4];
              last[8]=data[4];
              last[9]=data2[5]+data2[5];
              last[10]=data2[7];
          } 
          if((last[4]+last[2])!=0)
              last[11]=last[6]/(last[4]+last[2]);
              else
                  last[11]=0;
              last[12]=Math.max(last[7], last[8]);
              
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
          pair.setData(last);
          pair.setIP(ips, ipd);
          return pair;
      }
  }
  
  public Pair getPairFromIP(String ips, String ipd)
  {
      int id=-1;
      double[] data = new double[8];
      double[] data2 = new double[8];
      double[] last = new double[13];
      try
      {
          String query = "select source.cod as cod, source.ips as ips, source.ipd as ipd, count(ports.pd) as pd, "+
       " flags.syn as syn, flags.rst as rst, "+
       " flags.synack as synack, flags.fin as fin, "+
       " flags.icmp as icmp, count(distinct(window)) as window from scandetection.source inner join scandetection.ports on "+
       " ports.cod like source.cod "+
       " inner join scandetection.flags on flags.cod like source.cod "+
       " inner join scandetection.window on window.cod like source.cod "+
       " where source.ips like '"+ips+"' and source.ipd like '"+ipd+"';";
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            ips = rs.getString("ips");
            ipd = rs.getString("ipd");
            id = rs.getInt("cod");
            data[0] =rs.getDouble("pd");
            data[1] =rs.getDouble("syn");
            data[2] =rs.getDouble("synack");
            data[3] =rs.getDouble("rst");
            data[4] =rs.getDouble("fin");
            data[5] =rs.getDouble("window");
            data[6] =0;
            data[7] =rs.getDouble("icmp");
          }
          st.close();
          conn.close();
          int cod2 = checkExistingMain(ipd, ips);
          query = "select source.ips as ips, source.ipd as ipd, count(ports.pd) as pd, "+
       " flags.syn as syn, flags.rst as rst, "+
       " flags.synack as synack, flags.fin as fin, "+
       " flags.icmp as icmp, count(distinct(window)) as window  from scandetection.source inner join scandetection.ports on "+
       " ports.cod like source.cod "+
       " inner join scandetection.flags on flags.cod like source.cod "+
       " inner join scandetection.window on window.cod like source.cod "+
       " where source.cod like "+cod2;
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          st = (Statement) conn.createStatement();
          rs = st.executeQuery(query);
          randomList.add(cod2);
          while (rs.next())
          {
            data2[0] =rs.getDouble("pd");
            data2[1] =rs.getDouble("syn");
            data2[2] =rs.getDouble("synack");
            data2[3] =rs.getDouble("rst");
            data2[4] =rs.getDouble("fin");
            data2[5] =rs.getDouble("window");
            data2[6] =0;
            data2[7] =rs.getDouble("icmp");
          }
          st.close();
          conn.close();      
          if(data[1]>=data2[1] && data[2]>=data2[2])
          {
              last[0]=data[0];
              last[1]=data2[0];
              last[2]=data[1];
              last[3]=data[2];
              last[4]=data2[1];
              last[5]=data2[2];
              last[6]=data[3];
              last[7]=data[4];
              last[8]=data2[4];
              last[9]=data2[5]+data2[5];
              last[10]=data[7];
          }
          else
          {
              String aux = ipd;
              ipd=ips;
              ips=aux;
              last[0]=data2[0];
              last[1]=data[0];
              last[2]=data2[1];
              last[3]=data2[2];
              last[4]=data[1];
              last[5]=data[2];
              last[6]=data2[3];
              last[7]=data2[4];
              last[8]=data[4];
              last[9]=data2[5]+data2[5];
              last[10]=data2[7];
          } 
          if((last[4]+last[2])!=0)
              last[11]=last[6]/(last[4]+last[2]);
              else
                  last[11]=0;
              last[12]=Math.max(last[7], last[8]);
              
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
          pair.setData(last);
          pair.setIP(ips, ipd);
          return pair;
      }
  }
  
  public void getArray()
  {
      int id=-1;
      String ips ="";
      String ipd ="";
      int value =0;
      double[] data = new double[8];
      double[] data2 = new double[8];
      double[] last = new double[13];
      ArrayList<String> codes = new ArrayList<String>();
      //TrainSet ts = new TrainSet("/Users/francisco/Documents/Universidade/Mestrado/2Ano/TeseMestrado/Dataset/trainSet.ts", true);
      TrainSet ts = new TrainSet(path, true);
      try
      {
          String query = "select source.cod as cod from scandetection.source";
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            codes.add(rs.getInt("cod")+"");
          }
          st.close();
          conn.close();
          for(String cod : codes)
          {
              query =  " select source.ips as ips, source.ipd as ipd, count(ports.pd) as pd, "+
                       " flags.syn as syn, flags.rst as rst, "+
                       " flags.synack as synack, flags.fin as fin, "+
                       " flags.icmp as icmp, count(distinct(window)) as window from scandetection.source inner join scandetection.ports on "+
                       " ports.cod like source.cod "+
                       " inner join scandetection.flags on flags.cod like source.cod "+
                       " inner join scandetection.window on window.cod like source.cod "+
                       " where source.cod like "+cod;
              Class.forName(driver).newInstance();
              conn = DriverManager.getConnection(url+dbName,userName,password);
              st = (Statement) conn.createStatement();
              rs = st.executeQuery(query);
              while (rs.next())
              {
                ips = rs.getString("ips");
                ipd = rs.getString("ipd");
                data[0] =rs.getDouble("pd");
                data[1] =rs.getDouble("syn");
                data[2] =rs.getDouble("synack");
                data[3] =rs.getDouble("rst");
                data[4] =rs.getDouble("fin");
                data[5] =rs.getDouble("window");
                data[6] =0;
                data[7] =rs.getDouble("icmp");
              }
              st.close();
              conn.close();
              int cod2 = checkExistingMain(ipd, ips);
              query =  " select source.ips as ips, source.ipd as ipd, count(ports.pd) as pd, "+
                       " flags.syn as syn, flags.rst as rst, "+
                       " flags.synack as synack, flags.fin as fin, "+
                       " flags.icmp as icmp, count(distinct(window)) as window from scandetection.source inner join scandetection.ports on "+
                       " ports.cod like source.cod "+
                       " inner join scandetection.flags on flags.cod like source.cod "+
                       " inner join scandetection.window on window.cod like source.cod "+     
                       " where source.cod like "+cod2;
              Class.forName(driver).newInstance();
              conn = DriverManager.getConnection(url+dbName,userName,password);
              st = (Statement) conn.createStatement();
              rs = st.executeQuery(query);
              randomList.add(cod2);
              while (rs.next())
              {
                data2[0] =rs.getDouble("pd");
                data2[1] =rs.getDouble("syn");
                data2[2] =rs.getDouble("synack");
                data2[3] =rs.getDouble("rst");
                data2[4] =rs.getDouble("fin");
                data2[5] =rs.getDouble("window");
                data2[6] =0;
                data2[7] =rs.getDouble("icmp");
              }
              st.close();
              conn.close();      
              if(data[1]>=data2[1] && data[2]>=data2[2])
              {
                  last[0]=data[0];
                  last[1]=data2[0];
                  last[2]=data[1];
                  last[3]=data[2];
                  last[4]=data2[1];
                  last[5]=data2[2];
                  last[6]=data[3];
                  last[7]=data[4];
                  last[8]=data2[4];
                  last[9]=data2[5]+data2[5];
                  last[10]=data[7];
              }
              else
              {
                  String aux = ipd;
                  ipd=ips;
                  ips=aux;
                  last[0]=data2[0];
                  last[1]=data[0];
                  last[2]=data2[1];
                  last[3]=data2[2];
                  last[4]=data[1];
                  last[5]=data[2];
                  last[6]=data2[3];
                  last[7]=data2[4];
                  last[8]=data[4];
                  last[9]=data2[5]+data2[5];
                  last[10]=data2[7];
              } 
              if((last[4]+last[2])!=0)
              last[11]=last[6]/(last[4]+last[2]);
              else
                  last[11]=0;
              last[12]=Math.max(last[7], last[8]);
            if(last[0]>100 || last[9]>5)
                value=1;
            else
                value=0;
            System.out.print(cod+";"+last[0] +";"+ last[1] +";"+ last[2] +";"+ last[3] +";"+ last[4] +";"+ last[5] +";"+ last[6] +";"+ last[7] +";"+ last[8] +";"+ last[9] +";"+ last[10] +";"+ last[11] +";"+ last[12] +";"+ value + "\n");
            ts.createTrainSet(last[0] +";"+ last[1] +";"+ last[2] +";"+ last[3] +";"+ last[4] +";"+ last[5] +";"+ last[6] +";"+ last[7] +";"+ last[8] +";"+ last[9] +";"+ last[10] +";"+ last[11] +";"+ last[12] +";"+ value + "\n"); 
          }
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
       System.out.println("End Print");   
       ts.closeSet();
      }
  }
  
  
  
  
  
      /*
      try
      {
          String query = "select source.cod as cod, count(ports.pd) as pd, "+
       " flags.syn as syn, flags.rst as rst, "+
       " flags.synack as synack, flags.fin as fin, "+
       " flags.icmp as icmp from scandetection.source inner join scandetection.ports on "+
       " ports.cod like source.cod "+
       " inner join scandetection.flags on flags.cod like source.cod group by source.cod";
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
          Statement st = (Statement) conn.createStatement();
          ResultSet rs = st.executeQuery(query);
          while (rs.next())
          {
            id = rs.findColumn("cod");
            data +=rs.getDouble("pd")+";";
            data +=rs.getDouble("syn")+";";
            data +=rs.getDouble("synack")+";";
            data +=rs.getDouble("rst")+";";
            data +=rs.getDouble("fin")+";";
            data +=rs.getDouble("syn")-rs.getDouble("synack")+";";
            data +=rs.getDouble("syn")-rs.getDouble("fin")+";";
            data +=rs.getDouble("icmp");
            if(rs.getDouble("pd")>100 || rs.getDouble("icmp")>5)
                value=1;
            else
                value=0;
            ts.createTrainSet(data+";"+value+"\n");
            data="";
          }
          st.close();
          ts.closeSet();
          conn.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      finally
      {
          System.out.println("Acabou escrita");
          return id;
      }*/

  // </editor-fold>
}